package com.g2room.driving.grap.life;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.g2room.driving.grap.GenericGrap;
import com.g2room.driving.grap.life.model.po.LifeShop;
import com.g2room.driving.util.DateUtil;
import com.g2room.driving.util.ImageUtil;
import com.g2room.myuall.grap.ky.model.po.GrapErr;
import com.g2room.myuall.grap.univ.po.LifeNews;
import com.g2room.myuall.util.DbUtil;

public class GrapCqnongjiaData {
	private Connection conn = null;
	public String baseUrl = "http://www.cqnongjia.com/";
	private String source = "重庆农家网";
//	private static AtomicInteger id = new AtomicInteger(getMaxId());
	private static List<String> titles = new ArrayList<String>();
	public static void main(String[] args) throws Exception{
		GrapCqnongjiaData gkd = new GrapCqnongjiaData();
//		gkd.getKuaiBaoPage("http://www.cqnongjia.com/xiangcunyou.asp", 20);
//		gkd.getKuaiBaoPage("http://www.cqnongjia.com/caizhai.asp", 23);
//		gkd.getKuaiBaoPage("http://www.cqnongjia.com/diaoyuwenku.asp", 4);
		
//		gkd.getCaizaiyuanPage("http://www.cqnongjia.com/caizhaiyuan_list.asp?id=7", "葡萄", 4);
//		gkd.getCaizaiyuanPage("http://www.cqnongjia.com/caizhaiyuan_list.asp?id=16", "猕猴桃", 2);
//		gkd.getCaizaiyuanPage("http://www.cqnongjia.com/caizhaiyuan_list.asp?id=29", "香蕉", 1);
//		gkd.getCaizaiyuanPage("http://www.cqnongjia.com/caizhaiyuan_list.asp?id=34", "草莓", 1);
//		gkd.getCaizaiyuanPage("http://www.cqnongjia.com/caizhaiyuan_list.asp?id=30", "柚子", 1);
//		gkd.getCaizaiyuanPage("http://www.cqnongjia.com/caizhaiyuan_list.asp?id=24", "龙眼", 1);
//		gkd.getCaizaiyuanPage("http://www.cqnongjia.com/caizhaiyuan_list.asp?id=23", "无花果", 1);
//		gkd.getCaizaiyuanPage("http://www.cqnongjia.com/caizhaiyuan_list.asp?id=8", "蓝莓", 1);
//		gkd.getCaizaiyuanPage("http://www.cqnongjia.com/caizhaiyuan_list.asp?id=10", "杨梅", 1);
//		gkd.getCaizaiyuanPage("http://www.cqnongjia.com/caizhaiyuan_list.asp?id=11", "西瓜", 1);
//		gkd.getCaizaiyuanPage("http://www.cqnongjia.com/caizhaiyuan_list.asp?id=12", "桃园", 1);
//		gkd.getCaizaiyuanPage("http://www.cqnongjia.com/caizhaiyuan_list.asp?id=13", " 有机蔬菜", 1);
//		gkd.getCaizaiyuanPage("http://www.cqnongjia.com/chuidiao.asp?i=1", " 垂钓", 3);
//		gkd.getCaizaiyuanPage("http://www.cqnongjia.com/bishu.asp?i=1", " 避暑", 2);
		
		gkd.getNjlPage("http://www.cqnongjia.com/nongjiale.asp?i=1", "农家乐", 76);
    }
	
	private void getNjlPage(String url,String type, int page) {
		for(int i = 1; i< page+1; i++) {
			getNjlList(url + "&PID=" + i, type);
		}
	}

	private void getNjlList(String url, String type) {
		Document doc = null;
		try {
			doc = GenericGrap.getDoc(url, "gb2312");
		} catch(Exception e) {
			GrapErr ge = new  GrapErr();
			ge.setUrl(url);
			ge.setType("1");
			ge.setDescription("获取Score 错误："+e.getMessage());
			insert2ErrDb(ge);
			return;
		}
		List<LifeShop> lns = new ArrayList<LifeShop>();
		Elements cityels = doc.select("body>div>table table table table font[color=#FF9400]");
		Elements aels = doc.select("table table table table tr td p a");
		for(int i =0; i<aels.size(); i++) {
			if(!aels.get(i).html().contains("#0b73bb")) {
//				el.remove();
				aels.remove(i);
			}
		}
		if(cityels.size() != aels.size()) {
			System.out.println("=========");
		} else {
			for(int i=0; i< cityels.size(); i++) {
				System.out.println(cityels.get(i).text());
			}
		}
		
//		Elements tels = doc.select("table table table table");
//		if(null != tels && !tels.isEmpty()) {
//			for(Element el : tels) {
//				if(el.select("img").size() > 0) {
//					Elements els = el.select("table tbody tr");
//					for(int i=0; i<els.size(); i++) {
//						LifeShop ls = new LifeShop();
//						ls.setSource(source);
//						
//						Elements tdels = els.get(i).getElementsByTag("tr>td");
//						if(null != tdels && !tdels.isEmpty()) {
//							ls.setCityName(tdels.first().text().trim());
//							if(tdels.size() > 3) {
//								String name = tdels.get(2).getElementsByTag("a").first().attr("title").trim();
//								if(titles.contains(name)) {
//									i+=4;
//									continue;
//								}
//								ls.setName(name);
//								titles.add(ls.getName());
//								ls.setSourceUrl(baseUrl+ tdels.get(2).getElementsByTag("a").first().attr("href"));
//							}
//						}
//						
//						tdels = els.get(i+1).select("a img");
//						if(null != tdels && !tdels.isEmpty()) {
//							ls.setCover(tdels.first().attr("src"));
//						} else {
//							tdels = els.get(i+1).select("a font img");
//							if(null != tdels && !tdels.isEmpty()) {
//								ls.setCover(tdels.first().attr("src"));
//							}
//						}
//						tdels = els.get(i+1).select("font");
//						if(null != tdels && !tdels.isEmpty()) {
//							// 地址：重庆市渝北区统景风景区停车场旁边  电话：13896073415,023-67288155
//							String str = tdels.first().text();
//							if(str != null && str.length() > 0) {
//								String[] strs = str.split("电话：");
//								if(strs.length > 1) {
//									ls.setAddress(strs[0]);
//									ls.setPhone(strs[1]);
//								}
//							}
//						}
//
//						i+=4;
//						lns.add(ls);
//					}
//				}
//			}
//		}
		
		for(LifeShop li : lns) {
			try {
				getNjlDetail(li);
			} catch (Exception e) {
				lns.remove(li);
				e.printStackTrace();
			}
		}
		
		if(lns.size() > 0) insert2ShopDb(lns);
	}

	private LifeShop getNjlDetail(LifeShop li) {
		Document doc = null;
		try {
			doc = GenericGrap.getDoc(li.getSourceUrl(), "gb2312");
		} catch(Exception e) {
			GrapErr ge = new  GrapErr();
			ge.setUrl(li.getSourceUrl());
			ge.setType("1");
			ge.setContent(JSON.toJSONString(li));
			ge.setDescription("获取Score 错误："+e.getMessage());
			insert2ErrDb(ge);
			return null;
		}
		Elements els = doc.select("table table table table .zi p font");
		if(null != els && !els.isEmpty()) {
			String[] strs = els.first().text().split("加入时间：");
			if(strs.length > 1) {
				li.setPublishTime(strs[1]);
			}
			els = doc.select("#table2 table .wen font");
			if(null != els && !els.isEmpty()) {
				String content = els.first().html();
				content = content.replaceAll("uploadfile", baseUrl+"uploadfile");
				content = content.replaceAll("\\.\\./http", "http").replaceAll("/http", "http");
				li.setDescription(content);
			}
		}
		return li;
	}
	private void getCaizaiyuanPage(String url,String type, int page) {
		for(int i = 1; i< page+1; i++) {
			getCaizaiyuanList(url + "&PID=" + i, type);
		}
	}
	
	private void getCaizaiyuanList(String url, String type) {
		Document doc = null;
		try {
			doc = GenericGrap.getDoc(url, "gb2312");
		} catch(Exception e) {
			GrapErr ge = new  GrapErr();
			ge.setUrl(url);
			ge.setType("1");
			ge.setDescription("获取Score 错误："+e.getMessage());
			insert2ErrDb(ge);
			return;
		}
		List<LifeShop> lns = new ArrayList<LifeShop>();
		Elements els = doc.select("table table table table .tu p a");
		if(null != els && !els.isEmpty()) {
			for(Element el : els) {
				LifeShop ls = new LifeShop();
				ls.setSource(source);
				ls.setSourceUrl(baseUrl+ el.attr("href"));
				String title = el.attr("title");
				ls.setName(title.substring(title.indexOf("]")+1));
				ls.setProvinceName("重庆");
				if(title.indexOf("]") > -1) {
					ls.setCityName(title.substring(0, title.indexOf("]")).replace("[", ""));
				} else {
					System.out.println(title + "=" + ls.getSourceUrl());
				}
				// 获取图片
				Elements imgels = el.getElementsByTag("img");
				if(null == imgels || imgels.isEmpty()) {
					continue;
				} else {
					ls.setCover(baseUrl + imgels.first().attr("src"));
				}
				ls.setTypeName(type);
				lns.add(ls);
			}
		}
		
		for(LifeShop li : lns) {
			try {
				getCaizaiyuanDetail(li);
			} catch (Exception e) {
				lns.remove(li);
				e.printStackTrace();
			}
		}
		
		if(lns.size() > 0) insert2ShopDb(lns);
	}
	
	private LifeShop getCaizaiyuanDetail(LifeShop li) {
		Document doc = null;
		try {
			doc = GenericGrap.getDoc(li.getSourceUrl(), "gb2312");
		} catch(Exception e) {
			GrapErr ge = new  GrapErr();
			ge.setUrl(li.getSourceUrl());
			ge.setType("1");
			ge.setContent(JSON.toJSONString(li));
			ge.setDescription("获取Score 错误："+e.getMessage());
			insert2ErrDb(ge);
			return null;
		}
		Elements els = doc.select("table table table table .zi p font");
		if(null != els && !els.isEmpty()) {
			String[] strs = els.first().text().split("加入时间：");
			if(strs.length > 1) {
				li.setPublishTime(strs[1]);
			}
			els = doc.select("#table2 table .wen font");
			if(null != els && !els.isEmpty()) {
				String content = els.first().html();
				content = content.replaceAll("uploadfile", baseUrl+"uploadfile");
				content = content.replaceAll("\\.\\./http", "http").replaceAll("/http", "http");
				li.setDescription(content);
			}
		}
		return li;
	}
	
	private void getKuaiBaoPage(String url, int page) {
		for(int i = 1; i< page+1; i++) {
			getKuaiBaoList(url + "?PID=" + i);
		}
	}
	private void getKuaiBaoList(String url) {
		Document doc = null;
		try {
			doc = GenericGrap.getDoc(url, "gb2312");
		} catch(Exception e) {
			GrapErr ge = new  GrapErr();
			ge.setUrl(url);
			ge.setType("1");
			ge.setDescription("获取Score 错误："+e.getMessage());
			insert2ErrDb(ge);
			return;
		}
		List<LifeNews> lns = new ArrayList<LifeNews>();
		Elements els = doc.select("#table2 table table tbody tr .book a");
		if(null != els && !els.isEmpty()) {
			for(Element el : els) {
				LifeNews ln = new LifeNews();
				ln.setSource(source);
				ln.setSourceUrl(baseUrl+ el.attr("href"));
//				String title = el.attr("title");
//				try {
//					title = new String(title.getBytes(), "utf-8");
//				} catch (UnsupportedEncodingException e) {
//					// TODO Auto-generated catch block
//					e.printStackTrace();
//				}
				ln.setTitle(el.attr("title"));
				lns.add(ln);
			}
		}
		
		for(LifeNews li : lns) {
			try {
				getKuaiBaoDetail(li);
			} catch (Exception e) {
				lns.remove(li);
				e.printStackTrace();
			}
		}
		
		if(lns.size() > 0) insert2NewsDb(lns);
	}

	private LifeNews getKuaiBaoDetail(LifeNews li) {
		Document doc = null;
		try {
			doc = GenericGrap.getDoc(li.getSourceUrl(), "gb2312");
//			doc = GenericGrap.getDocumentByIp(li.getSourceUrl());
		} catch(Exception e) {
			GrapErr ge = new  GrapErr();
			ge.setUrl(li.getSourceUrl());
			ge.setType("1");
			ge.setContent(JSON.toJSONString(li));
			ge.setDescription("获取Score 错误："+e.getMessage());
			insert2ErrDb(ge);
			return null;
		}
		Elements els = doc.select("table table table table .zi p font");
		if(null != els && !els.isEmpty()) {
			String[] strs = els.first().text().split("加入时间：");
			if(strs.length > 1) {
				try {
					li.setPublishTime(DateUtil.string2Date(strs[1], "yyyy-MM-dd HH:mm:ss"));
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			els = doc.select("#table2 table .wen font");
			if(null != els && !els.isEmpty()) {
				String content = els.first().html();
				content = content.replaceAll("uploadfile", baseUrl+"uploadfile");
				content = content.replaceAll("\\.\\./http", "http").replaceAll("/http", "http");
				li.setContent(content);
			}
			// 判断是否有图片，有则获取第一张图片作为封面
			els = els.first().getElementsByTag("img");
			if(null != els && !els.isEmpty()) {
				String content = els.first().attr("src");
				content = content.replaceAll("uploadfile", baseUrl+"uploadfile");
				content = content.replaceAll("\\.\\./http", "http").replaceAll("/http", "http");
				li.setCover(content);
				// 获取宽度高度
				Map<Integer, Integer> map = ImageUtil.getImgWidth(li.getCover());
				if(null != map) {
					for(int key : map.keySet()) {
						li.setCoverWidth(key);
						li.setCoverHeight(map.get(key));
					}
				}
			}
		}
		return li;
	}
	

	public void insert2ShopDb(List<LifeShop> lses) {
		String sql = "insert into life_shop(source_url,category_name,type_name,flavor,address,category_ids,shop_hours,name,source,star,cover,traffic,province_name,spend_perhead,service,environment,phone,description,publish_time,city_name) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = getConn().prepareStatement(sql);
			for(LifeShop cc : lses) {
				ps.setString(1, cc.getSourceUrl());
				ps.setString(2, cc.getCategoryName());
				ps.setString(3, cc.getTypeName());
				ps.setString(4, cc.getFlavor());
				ps.setString(5, cc.getAddress());
				ps.setString(6, cc.getCategoryIds());
				ps.setString(7, cc.getShopHours());
				ps.setString(8, cc.getName());
				ps.setString(9, cc.getSource());
				ps.setString(10, cc.getStar());
				ps.setString(11, cc.getCover());
				ps.setString(12, cc.getTraffic());
				ps.setString(13, cc.getProvinceName());
				ps.setString(14, cc.getSpendPerhead());
				ps.setString(15, cc.getService());
				ps.setString(16, cc.getEnvironment());
				ps.setString(17, cc.getPhone());
				ps.setString(18, cc.getDescription());
				ps.setString(19, cc.getPublishTime());
				ps.setString(20, cc.getCityName());
				ps.addBatch();
			}
			ps.executeBatch();
			System.out.print("，插入数据库"+lses.size());
		} catch (Exception e) {
			e.printStackTrace();
			GrapErr ge = new GrapErr();
			ge.setType("5");
			ge.setContent(JSON.toJSONString(lses));
			ge.setDescription("insert DB 错误：" + e.getMessage());
			insert2ErrDb(ge);
			return;
		} finally {
			if (null != ps) {
				try {
					ps.close();
				} catch (SQLException e) {
				}
			}
		}
	}
	public void insert2NewsDb(List<LifeNews> nis) {
		String sql = "insert into life_news_tour(author,title,summary,content,publish_time,source,source_url,type_name,create_time,cover_height,cover_width,cover) values(?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = getConn().prepareStatement(sql);
			List<String> ids = new ArrayList<String>();
			List<String> idxNis = new ArrayList<String>();
			for(LifeNews ni : nis) {
				ps.setString(1, ni.getAuthor());
				ps.setString(2, ni.getTitle());
				ps.setString(3, ni.getSummary());
				ps.setString(4, ni.getContent());
				ps.setString(5, null == ni.getPublishTime() ? DateUtil.date2String(new Date(), "yyyy-MM-dd HH:mm:ss") : DateUtil.date2String(ni.getPublishTime(), "yyyy-MM-dd HH:mm:ss"));
				
				ps.setString(6, ni.getSource());
				ps.setString(7, ni.getSourceUrl());
				ps.setString(8, ni.getTypeName());
				ps.setString(9, DateUtil.date2String(new Date(), "yyyy-MM-dd HH:mm:ss"));
				ps.setInt(10, ni.getCoverHeight());
				ps.setInt(11, ni.getCoverWidth());
				ps.setString(12, ni.getCover());
//				ps.setInt(13, ni.getId());
//				ps.setInt(14, ni.getTypeId());
				ps.addBatch();
				ids.add(ni.getId() + "");
				idxNis.add(JSON.toJSONString(ni, SerializerFeature.WriteDateUseDateFormat).replace("\n", ""));
			}
			ps.executeBatch();
			System.out.print("，插入数据库"+ids.size());
		} catch (Exception e) {
			e.printStackTrace();
			GrapErr ge = new GrapErr();
			ge.setType("5");
			ge.setContent(JSON.toJSONString(nis));
			ge.setDescription("insert DB 错误：" + e.getMessage());
			insert2ErrDb(ge);
			return;
		} finally {
			if (null != ps) {
				try {
					ps.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	
	private Connection getConn() {
		if(null == conn) conn = DbUtil.getNewsConn();
		return conn;
	}
	
	public void insert2ErrDb(GrapErr ge) {
		PreparedStatement grapPs = null;
		try {
			grapPs = DbUtil.getComConn().prepareStatement("insert into grap_err(description,url,content,type) values(?,?,?,?)");
			grapPs.setString(1, ge.getDescription());
			grapPs.setString(2, ge.getUrl());
			grapPs.setString(3, ge.getContent());
			grapPs.setString(4, ge.getType());
			grapPs.execute();
			grapPs.close();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(null != grapPs) {
				try {
					grapPs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public static List<GrapErr> getErrs() {
		List<GrapErr> univs = new ArrayList<GrapErr>();
		try {
			Connection connection = DbUtil.getComConn();
			Statement statement = connection.createStatement();
			String sql = "select * from grap_err";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				GrapErr univ = new GrapErr();
				univ.setId(rs.getInt("id"));
				univ.setUrl(rs.getString("url"));
				univ.setType(rs.getString("type"));
				univ.setContent(rs.getString("content"));
				univs.add(univ);
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return univs;
	}
	
}
